/*++

Copyright (c) 2007  Microsoft Corporation 

Filename: 

    DBManager.cs 

Abstract: 

    This file contains definition of DataAccess Wrapper functions
     
--*/
using System.Security.Permissions;

[assembly: SecurityPermission(SecurityAction.RequestMinimum, Execution = true)]

namespace NRTMAddIn
{
    #region Namespaces

    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Configuration;
    using System.Globalization;

    #endregion Namespace

    /// <summary>
    /// DataAccess class is used to interact with data base.
    /// </summary>
    public class DBManager : IDisposable
    {
        #region Variables

        //Stores database related data
        private string connectionString;
        private SqlConnection sqlConnection;
        private bool disposed;

        #endregion

        #region Constructor / Destructor

        /// <summary>
        /// Constructor of the class.
        /// </summary>
        public DBManager(string strConn)
        {
            try
            {
                //get connection string
                this.connectionString = strConn;
                this.sqlConnection = new SqlConnection(connectionString);

                //open the connection
                this.OpenConnection();
            }
            catch (SqlException)
            {
                throw;
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// Destructor for the class
        /// </summary>
        ~DBManager()
        {
            Dispose(false);
        }

        /// <summary>
        /// Dispose method
        /// </summary>
        public void Dispose()
        {
            Dispose(true);
        }

        /// <summary>
        /// Dispose method with option to supress finalization
        /// </summary>
        /// <param name="disposing">True to supress finalizarion</param>
        protected virtual void Dispose(bool disposing)
        {
            if (!disposed)
            {
                // Dispose of resources held by this instance.
                this.CloseConnection();

                // Set the sentinel.
                disposed = true;

                // Suppress finalization of this disposed instance.
                if (disposing)
                {
                    GC.SuppressFinalize(this);
                }
            }
        }

        #endregion Constructor

        #region Methods

        /// <summary>
        /// This method execute StoreProceedure and on successful it returns dataset.
        /// and if any exception occurs it throws new exception
        /// </summary>
        /// <param name="cmdDataAccess">Sql Command object to execute SP.</param>
        /// <returns> It ruturns DataSet</returns>
        public DataSet GetDataSet(SqlCommand cmdDataAccess)
        {
            SqlDataAdapter sdaDataAccess = null;
            DataSet dsDataAccess = new DataSet();
            dsDataAccess.Locale = CultureInfo.InvariantCulture;

            try
            {
                //Create Connection
                this.OpenConnection();

                if (cmdDataAccess == null)
                {
                    throw new ArgumentNullException(string.Empty);
                }

                cmdDataAccess.CommandType = CommandType.StoredProcedure;
                cmdDataAccess.Connection = this.sqlConnection;
                cmdDataAccess.CommandTimeout = 300;
                sdaDataAccess = new SqlDataAdapter(cmdDataAccess);
                sdaDataAccess.Fill(dsDataAccess, "DataAccess");
                cmdDataAccess.Parameters.Clear();

                //Close Connection
                this.CloseConnection();
            }
            catch (SqlException)
            {
                throw;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                //close the connection
                this.CloseConnection();
            }
            return dsDataAccess;
        }

        /// <summary>
        /// This method execute StoreProceedure and on successfull it returns true value
        ///  and if any exception occurs it throws new exception
        /// </summary>
        /// <param name="cmdDataAccess">Sql Command object to execute SP.</param>
        /// <returns>on successfull it returns true value</returns>
        public string ExecuteQuery(SqlCommand cmdDataAccess)
        {
            string sReturn = string.Empty;
            try
            {
                //Create Connection
                this.OpenConnection();

                if (cmdDataAccess == null)
                {
                    throw new ArgumentNullException(sReturn);
                }
                cmdDataAccess.CommandType = CommandType.Text;
                cmdDataAccess.Connection = this.sqlConnection;
                cmdDataAccess.CommandTimeout = 300;
                sReturn = Convert.ToString(cmdDataAccess.ExecuteNonQuery(), CultureInfo.InvariantCulture);
            }
            catch (SqlException)
            {
                throw;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                //close the connection
                this.CloseConnection();
            }
            return sReturn;
        }

        /// <summary>
        /// Opens the database connection taking care of auto 
        /// connection management
        /// </summary>
        public void OpenConnection()
        {
            // if Connection object is null then instantiate it, if not 
            // check its state
            if (this.sqlConnection != null)
            {
                // If connection state is not open then close it
                if (this.sqlConnection.State != ConnectionState.Open)
                {
                    //Open a connection
                    this.sqlConnection.Open();
                }
                else
                {
                    // else nothing
                }
            }
            else
            {
                // Instantiating Connection object.
                this.sqlConnection = new SqlConnection(this.connectionString);

                // Opening Database Connection.
                this.sqlConnection.Open();
            }
        }

        /// <summary>
        /// Closes database connection.
        /// </summary>
        public void CloseConnection()
        {
            // Check whether connection object is null or not, if it is 
            // then instantiate it.
            if (this.sqlConnection != null)
            {
                // If the state of the connection is not 'Closed' 
                // then close it.
                if (this.sqlConnection.State != ConnectionState.Closed)
                {
                    // Close the connection
                    this.sqlConnection.Close();
                }
                else
                {
                    // else nothing                    
                }

                this.sqlConnection = null;
            }
        }

        #endregion Method
    }
}
